package net.Member.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;


public class MemberDAO {
	Connection con;
	PreparedStatement pstmt;
	ResultSet rs;
	DataSource ds;
	
	 public MemberDAO(){  //DB사용을위해 정의
		try {
			Context init = new InitialContext();
			ds=(DataSource)init.lookup("java:comp/env/jdbc/project");
		} catch (Exception e) {
			// TODO: handle exception]
			e.printStackTrace();
		}
	}
	public int memberlogincheck(String member_id, String member_passwd){
		int check = -1; // 아뒤없음
		String sql=""; // Sql정의를 위해 선언.
		try {
			con=ds.getConnection(); //DB연결
			
			sql="SELECT st_passwd FROM student WHERE st_num = ?"; //student 비밀번호조회.)
			pstmt=con.prepareStatement(sql); 
			pstmt.setString(1, member_id); // sql의 member__id를 구해오기위해정의.
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				if(member_passwd.equals(rs.getString("st_passwd"))){ //student 요청한 비번과 DB의 비번이 맞는지 비교
					check =0; // 로그인
				}else {
					check = 1; //비번없음
				}
			}else{
				sql="SELECT te_passwd FROM teacher WHERE te_num =?"; // teacher비밀번호 조회.
				pstmt=con.prepareStatement(sql);
				pstmt.setString(1, member_id);
				
				rs=pstmt.executeQuery();
				if(rs.next()){
					if(member_passwd.equals(rs.getString("te_passwd"))){ //teacher에 요청한 비번과 DB의 비번이 맞는지 비교
						check = 0; //로그인
					}else {
						check = 1; //비번틀림
					}
				}else{
					check =-1; // 아뒤없음.
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {  // ������ DB�� �ݴ´�.
			if(rs!=null)try{rs.close();}catch(Exception e){};
			if(pstmt!=null)try{pstmt.close();}catch(Exception e){};
			if(con!=null)try{con.close();}catch(Exception e){};
		}
		
		return check;
		
	}
	public String getMemberLevel(String member_id_level){
		String level =""; // 등급 저장할 공간
		String sql="";
		try {
			con=ds.getConnection();
			sql="SELECT level FROM student WHERE st_num=?"; // 학생테이블에서의 레벨값이 잇는지 조회.
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, member_id_level);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				level=rs.getString("level"); // 데이터가 있으면 등급을 저장한다.
			}else{
				sql="SELECT level FROM teacher WHERE te_num=?"; // 선생테이블에서의 레벨값이 있는지 조회
				pstmt=con.prepareStatement(sql);
				pstmt.setString(1, member_id_level);
				rs=pstmt.executeQuery();
				
				if(rs.next()){
					level=rs.getString("level");
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(rs!=null)try{rs.close();}catch(Exception e){};
			if(pstmt!=null)try{pstmt.close();}catch(Exception e){};
			if(con!=null)try{con.close();}catch(Exception e){};
		}
		return level;
		
	}
	public int totel(){
		int totel =0;
		int st_totel=0;
		int te_totel=0;
		String sql="";
		try {
			con =ds.getConnection();
			sql="SELECT COUNT(te_num) FROM teacher WHERE te_num != 'admin'"; // admin을 제외한 회원수를 구하기
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				te_totel=rs.getInt(1);
			}
			sql="SELECT COUNT(st_num) FROM student"; // 학생의 회원수 구하기
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				st_totel = rs.getInt(1);
			}
			
			totel = st_totel + te_totel; //총회원수 구하기
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(rs!=null)try{rs.close();}catch(Exception e){};
			if(pstmt!=null)try{pstmt.close();}catch(Exception e){};
			if(con!=null)try{con.close();}catch(Exception e){};
		}
		return totel;
	}
	
	public int st_totel(){ //학생의 총인원수 구하기
		int st_totel=0;
		String sql="SELECT COUNT(st_num) FROM student";
		try {
			con =ds.getConnection();
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				st_totel =  rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(rs!=null)try{rs.close();}catch(Exception e){};
			if(pstmt!=null)try{pstmt.close();}catch(Exception e){};
			if(con!=null)try{con.close();}catch(Exception e){};
		}
		
		return st_totel;
		
	}
	
	public int te_totel(){ //교직원의 총인원구하기
		int te_totel=0;
		String sql="";
		try {
			con =ds.getConnection();
			sql="SELECT COUNT(te_num) FROM teacher WHERE te_num != 'admin'";
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			if(rs.next()){
				te_totel = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(rs!=null)try{rs.close();}catch(Exception e){};
			if(pstmt!=null)try{pstmt.close();}catch(Exception e){};
			if(con!=null)try{con.close();}catch(Exception e){};
		}
		return te_totel;
	}
}
